import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()

data={
    'id': '20120001',
    'name': 'Bob',
    'age': 22
}

table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))

sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys,values=values)
# for i in data:
#     print(i)
# update = ', '.join(["{key} = %s".format(key=key) for key in data]) #错:{key} = %s 前面少打了一个空格,或者在UPDATE后面打空格
update = ', '.join([" {key} = %s".format(key=key) for key in data]) #列表推导式
sql += update
# print(sql)
try:
    if cursor.execute(sql, tuple(data.values())*2):
        print("Successful")
        db.commit()
except Exception as e:
    print("Failed, reason:",e)
    db.rollback()
db.close()

'''
注意1：Failed, reason: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATEid = '20120001', name = 'Bob', age = 22' at line 1")
    列表推导式少写了一个空格
分析：
    ON DUPLICATE KEY UPDATE：意思是如果主键已存在，就执行更新操作
'''